package com.example.dao;

import com.example.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

@Repository
public interface TreeDao extends Mapper<Tree> {

    @Select("select tree.* ,tree_type_name.treetypename as treetypename from tree inner join tree_type_name on tree.treetypeid = tree_type_name.tree_type_id order by tree.username")
    List<TreeAndTreeName> loadTree(@Param("params") Params params);

    @Select("select * from tree")
    List<Tree> findTreeInfo();

    @Select("select tree.* ,tree_type_name.treetypename as treetypename from tree left join tree_type_name on tree.treetypeid = tree_type_name.tree_type_id where tree.tree_id = #{treeId} ")
    TreeAndTreeName findById(@Param(value = "treeId") Integer treeId);

    List<TreeAndTreeName> findTree(@Param(value = "params") Params params);

    List<TreeAndTreeName> findByTreeType(@Param(value = "params") Params params);

    @Update("update tree set treename = #{treename},treetime = #{treetime},treeprice = #{treeprice},treetypeid = (select tree_type_name.tree_type_id from tree_type_name where treetypename = #{treetypename}) where tree_id = #{treeId}")
    void treeEdit(TreeAndTreeName tree);

    @Delete("delete from tree where tree_id = #{treeId}")
    void deleteById(Integer treeId);

    @Select("select tree.*,tree_type_name.treetypename as treetypename from tree left join tree_type_name on tree.treetypeid = tree_type_name.tree_type_id order by tree.treetime desc")
    List<TreeAndTreeName> findByYearDesc(Params params);

    @Select("select tree.*,tree_type_name.treetypename as treetypename from tree left join tree_type_name on tree.treetypeid = tree_type_name.tree_type_id order by tree.treetime")
    List<TreeAndTreeName> findByYearAsc(Params params);

    @Select("select tree.*,tree_type_name.treetypename as treetypename from tree left join tree_type_name on tree.treetypeid = tree_type_name.tree_type_id order by tree.treeprice desc")
    List<TreeAndTreeName> findByPriceDesc(Params params);

    @Select("select tree.*,tree_type_name.treetypename as treetypename from tree left join tree_type_name on tree.treetypeid = tree_type_name.tree_type_id order by tree.treeprice")
    List<TreeAndTreeName> findByPriceAsc(Params params);

    @Update("update tree set sellnum = sellnum - #{treenumber} where tree_id = #{treeId}")
    void updatetree(Integer treeId, Integer treenumber);

    //通过树苗交换修改树苗数量
    @Update("update tree set sellnum = #{treenumber} where tree_id = #{treeId}")
    void updateTree(Integer treeId, int treenumber);

    List<TreeAndTreeName> findOtherTree(@Param(value = "username")String username,@Param(value = "params") Params params);

    @Select("select sum(tree.sellnum) as treeNumber from tree ")
    int loadTreeNumber();

    @Select("select count(distinct treetypeid) as typeNumber from tree")
    int loadTypeNumber();

    @Select("select * from collect where treeId = #{treeId} and username = #{username}")
    Collect findBytreeIdAndUsername(@Param(value = "treeId") Integer treeId,@Param(value = "username") String username);

    @Insert("insert into collect (collect_id, treeId, treename, shopper, username, treetypeid, collecttime) " +
            "values (#{collectId},#{treeId},#{treename},#{shopper},#{username},#{treetypeid},#{collecttime})")
    void collectTree(Collect collect);

    List<CollectAndTreeTypeName> findCollect(@Param(value = "params") Params params,@Param(value = "user") User user);

    List<CollectAndTreeTypeName> businessFindCollect(@Param(value = "params") Params params,@Param(value = "user") User user);

    @Delete("delete from collect where collect_id = #{collectId}")
    void cancelCollect(@Param(value = "collectId") Integer collectId);

    //通过树苗名称找到树苗信息
    @Select("select * from tree where treename = #{treename} and username = #{username}")
    List<Tree> findTreeByName(@Param(value = "treename") String treename,@Param(value = "username") String username);

    @Select("select treetypeid, sum(sellnum) as sellnum from tree group by treetypeid order by treetypeid")
    List<Tree> getSysData();

    @Select("select treetypeid, sum(sellnum) as sellnum from tree where username = #{username} group by treetypeid order by treetypeid")
    List<Tree> getUserData(String username);

    @Select("select treetypeid, floor(avg(treeprice)) as treeprice from tree group by treetypeid order by treetypeid")
    List<Tree> getAvgPriceData();

    @Select("select treetypeid, floor(avg(treeprice)) as treeprice from tree where username = #{username} group by treetypeid order by treetypeid")
    List<Tree> getPersonalPriceData(String username);

    //商家在售树苗查询
    @Select("select tree.* from tree left join user on tree.username = user.username where user.user_id = #{userId}")
    List<Tree> findBuinessTreeInfo(@Param(value = "userId") Integer userId);

    //通过树苗种类名称得到树苗种类id
    @Select("select tree_type_id from tree_type_name where treetypename = #{treetypename}")
    int findtypeid(@Param(value = "treetypename") String treetypename);

    //新增树苗到数据库
    @Insert("insert into tree (tree_id, username, treename, treetime, treeprice, treeproduce, treedetailinfo, treetypeid, sellnum, img)" +
            " VALUES (#{treeId},#{username},#{treename},#{treetime},#{treeprice},#{treeproduce},#{treedetailInfo},#{treetypeid},#{sellnum},#{img})")
    void addNewTree(Tree treeinfo);

    @Delete("delete from tree where tree_id = #{treeId}")
    void delTree(Integer treeId);

    //通过树苗名称和用户名称查询树苗id
    @Select("select tree.tree_id from tree where username = #{username} and treename = #{treename}")
    Integer findTreeIdByUsernameAndTreeName(String username, String treename);

    //添加交换树苗信息
    @Insert("insert into changetree (changetree_id, ordercode, treeid, number, summoney, treeprice) " +
            "VALUES (#{changetreeId},#{orderCode},#{treeId},#{number},#{sumMoney},#{treePrice}) ")
    void addChangeTree(ChangeTree changeTree);

    //通过树苗id获取树苗信息
    @Select("select * from tree where tree_id = #{treeId}")
    Tree findTreeByTreeId(int treeId);

    //通过orderCode找到changetree的树苗信息
    @Select("select * from changetree where ordercode = #{orderCode}")
    ChangeTree findChangeTreeByOrderCode(String orderCode);

    //通过用户名称查找树苗信息
    @Select("select * from tree where username = #{username}")
    List<Tree> findTreeByUsername(String username);

    //新增树苗
    @Insert("insert into tree (tree_id, username, treename, treetime, treeprice, treeproduce, treedetailinfo, treetypeid, sellnum, img) " +
            "VALUES (#{treeId},#{username},#{treename},#{treetime},#{treeprice},#{treeproduce},#{treedetailInfo},#{treetypeid},#{sellnum},#{img})")
    void addTree(Tree newTree);

    //通过商家名称和树苗名称查询树苗信息
    @Select("select * from tree where username = #{username} and treename = #{treename}")
    Tree findTreeByUsernameAndTreeName(String username, String treename);


}
